package sams.ck.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import sams.ck.entity.Student;
import sams.ck.entity.stuPage;
import sams.ck.util.DBUtil;

public class stuDao {
	//查询学生数据表中所有数据
	public List<Student>findAllStudents(){
		//定义一个学生实体类数组
		List<Student>stus=new ArrayList<Student>();
		try {
			//获取数据库连接对象
			Connection conn = DBUtil.getConnection();
			//定义SQL语句
			String sql = "select * from students";
			//获取PreparedStatment对象  负责操作数据库sql语句
			PreparedStatement prep = conn.prepareStatement(sql);
			//执行sql语句  获取查询数据后的结果集对象
			ResultSet rs=prep.executeQuery();
			//迭代结果集对象 将查询输出的数据封装到Stus对象当中
			while(rs.next()) {
				Student stu = new Student();
				stu.setStuId(rs.getString("number"));
				stu.setStuName(rs.getString("name"));
				stu.setStuClass(rs.getString("class"));
				stu.setChineseScore(rs.getDouble("chinese"));
				stu.setEnglisScore(rs.getDouble("english"));
				stu.setMathScore(rs.getDouble("math"));
				//将封装好的stu对象 添加到list集合中
				stus.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stus;
	}
	//添加学生成绩信息
	public void addStudent(Student stu) {
		try {
			//获取数据库连接
			Connection conn = DBUtil.getConnection();
			//定义SQL语句
			String sql = "insert into students(number,name,class,chinese,math,english)values(?,?,?,?,?,?)";
			//获取PreparedStatement对象
			PreparedStatement prep = conn.prepareStatement(sql);
			//添加占位符参数
			prep.setString(1, stu.getStuId());
			prep.setString(2, stu.getStuName());
			prep.setString(3, stu.getStuClass());
			prep.setDouble(4, stu.getChineseScore());
			prep.setDouble(5, stu.getMathScore());
			prep.setDouble(6, stu.getEnglisScore());
			//执行SQL
			prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	//删除学生成绩信息
	public void deleteStudent(String stuId) {
		try {
			//获取数据库连接
			Connection conn = DBUtil.getConnection();
			//定义SQL语句
			String sql = "delete from students where number=?";
			//获取PreparedStatement对象
			PreparedStatement prep = conn.prepareStatement(sql);
			//将传递的参数 注入到占位符中
			prep.setString(1,stuId);
			//执行SQL
			prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//修改学生信息前提是通过要修改的学生学号获取学生信息
	public Student findByStuId(String stuId) {
		Student stu = null;
		try {
			//获取数据库连接
			Connection conn = DBUtil.getConnection();
			//定义SQL语句
			String sql = "select * from students where number=?";
			//获取PreparedStatement对象
			PreparedStatement prep = conn.prepareStatement(sql);
			//将传递的参数 注入到占位符中
			prep.setString(1,stuId);
			//执行SQL获取结果集对象
			ResultSet rs = prep.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuId(rs.getString("number"));
				stu.setStuName(rs.getString("name"));
				stu.setStuClass(rs.getString("class"));
				stu.setChineseScore(rs.getDouble("chinese"));
				stu.setEnglisScore(rs.getDouble("english"));
				stu.setMathScore(rs.getDouble("math"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stu;
	}
	//修改学生信息
	public void updateStudent(Student stu) {
		try {
			//获取数据库连接
			Connection conn = DBUtil.getConnection();
			//定义SQL语句
			String sql = "update students set name=?,class=?,chinese=?,math=?,english=? where number=?";
			//获取PreparedStatement对象
			PreparedStatement prep = conn.prepareStatement(sql);
			//将传递的参数 注入到占位符中
			
			prep.setString(1,stu.getStuName());
			prep.setString(2,stu.getStuClass());
			prep.setDouble(3,stu.getChineseScore());
			prep.setDouble(4,stu.getMathScore());
			prep.setDouble(5,stu.getEnglisScore());
			prep.setString(6,stu.getStuId());
			
			//执行SQL获取结果集对象
			prep.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//查询学生成绩
	public Student seleStudent(String stuId) {
		Student stu = null;
		try {
			//获取数据库连接
			Connection conn = DBUtil.getConnection();
			//定义SQL语句
			String sql = "select name,Chinese,Math,English FROM students WHERE number=?";
			//获取PreparedStatement对象
			PreparedStatement prep = conn.prepareStatement(sql);
			//将传递的参数 注入到占位符中
			prep.setString(1,stuId);
			//执行SQL获取结果集对象
			ResultSet rs = prep.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuName(rs.getString("name"));
				stu.setChineseScore(rs.getDouble("chinese"));
				stu.setEnglisScore(rs.getDouble("english"));
				stu.setMathScore(rs.getDouble("math"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stu;
	}
	//分页查询
	public List<Student>findByPage(stuPage page) throws SQLException{
		List<Student>stus=new ArrayList<Student>();
		//链接数据库 
		Connection conn=DBUtil.getConnection();
		
		//定义sql语句
		String sql="select * from students limit ?,?";
		try {
			PreparedStatement prep=conn.prepareStatement(sql);
			prep.setInt(1, page.getBegin());
			prep.setInt(2, page.getPageSize());
			ResultSet rs=prep.executeQuery();
			//迭代结果集对象 将查询输出的数据封装到Stus对象当中  
			while(rs.next()){
				Student stu=new Student();
				stu.setStuId(rs.getString("number"));
				stu.setStuName(rs.getString("name"));
				stu.setStuClass(rs.getString("class"));
				stu.setChineseScore(rs.getDouble("chinese"));
				stu.setEnglisScore(rs.getDouble("english"));
				stu.setMathScore(rs.getDouble("math"));
				//将封装好的stu对象 添加到list集合中
				stus.add(stu);
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stus;
	}
	//查询总记录行数
	public int findRows(stuPage page) throws SQLException {
		int count=0;
		Connection conn=DBUtil.getConnection();

		String sql="select count(*) c from students";//给查询结果count(*)起别名为c
		try {
			PreparedStatement prep=conn.prepareStatement(sql);
			ResultSet rs=prep.executeQuery();
			if(rs.next()){
				count=rs.getInt("c");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
}
